package Controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.sql.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Random;
import java.util.TreeMap;

import javax.swing.JOptionPane;

import Model.Customer;
import Model.MenuItem;
import Model.Order;


public class DB {
    private static String username = "_445team11";
    private static String password = "TewEkic";
    private static String serverName = "cssgate.insttech.washington.edu";
    public static Connection conn;
    private List<Customer> customerList;
    private List<MenuItem> itemList;    
    
    public static void createConnection() throws SQLException {
        Properties connProps = new Properties();
        connProps.put("user", username);
        connProps.put("password", password);
        
        conn = DriverManager.getConnection("jdbc:" + "mysql" + "://"
				+ serverName + "/", connProps);
				
		System.out.println("Connected to database");
    }
    
    public List<Customer> getCustomers() throws SQLException {
        if (conn == null) {
            createConnection();
        }
        Statement s = null;
        String query = "select userID, name, addressStreet, addressCity, addressState, addressZip, phone from _445team11.Customer ";
                
        customerList = new ArrayList<Customer>();
        try {
            s = conn.createStatement();
            ResultSet rs = s.executeQuery(query);
            while (rs.next()) {
                String userID = rs.getString("userID");
                String name = rs.getString("name");
                String addressStreet = rs.getString("addressStreet");
                String addressCity = rs.getString("addressCity");
                String addressState = rs.getString("addressState");
                String addressZip = rs.getString("addressZip");
                String phone = rs.getString("phone");
                Customer customer = new Customer(userID, name, addressStreet, addressCity, addressState, addressZip, phone);
                customerList.add(customer);
            }
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            if (s != null) {
                s.close();
            }
        }
        return customerList;
    }
    
    public List<Customer> getCustomers(String userID) {
        List<Customer> filterList = new ArrayList<Customer>();
        try {
            customerList = getCustomers();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for (Customer cus : customerList) {
            if (cus.getUserID().toLowerCase().contains(userID.toLowerCase())) {
                filterList.add(cus);
            }
        }
        return filterList;
    }
    
    public void addCustomer(Customer cus) {
        String sql = "insert into _445team11.Customer values " + "(?, ?, ?, ?, ?, ?, ?);";
        PreparedStatement prepState = null;
        try {
            prepState = conn.prepareStatement(sql);
            prepState.setString(1, cus.getName());
            prepState.setString(2, cus.getUserID());
            prepState.setString(3, cus.getStreet());
            prepState.setString(4, cus.getCity());
            prepState.setString(5, cus.getState());
            prepState.setString(6, cus.getZip());
            prepState.setString(7, cus.getPhone());
            prepState.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            e.printStackTrace();
        }
    }
    
    public void updateCustomer(Customer cus) {
    	
    	String sql = "UPDATE _445team11.Customer SET name='"+cus.getName()+
    											   "',addressStreet='"+cus.getStreet()+
    											   "',addressCity='"+cus.getCity()+
    											   "',addressState='"+cus.getState()+
    											   "',addressZip='"+cus.getZip()+
    											   "',phone='"+cus.getPhone()+
    											   "' WHERE userID='"+cus.getUserID()+"'";
    	PreparedStatement prepState = null;
        try {
            prepState = conn.prepareStatement(sql);

            prepState.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            e.printStackTrace();
        }
    	
    }

    public List<MenuItem> getFavorites(Customer cust) {
    	
	    String sql = "SELECT DISTINCT name, price, itemID, description FROM _445team11.Item NATURAL JOIN _445team11.Favorites"
	    			+ " WHERE Item_itemID = itemID AND Customer_userID = '" + cust.getUserID() + "'";
        ArrayList<MenuItem> itemList = new ArrayList<MenuItem>();
        
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                int itemID = rs.getInt("itemID");
                String name = rs.getString("name");
                String description = rs.getString("description");
                double price = rs.getDouble("price");
                MenuItem item = new MenuItem(itemID, name, description, price);
                itemList.add(item);
            }
        } catch (SQLException e) {
            System.out.println(e);
            e.printStackTrace();
        }
        return itemList;
    }
    
    public List<MenuItem> getMenuItems() throws SQLException {
        if (conn == null) {
            createConnection();
        }
        Statement s = null;
        String query = "select itemID, name, description, price from _445team11.Item ";
        
        itemList = new ArrayList<MenuItem>();
        try {
            s = conn.createStatement();
            ResultSet rs = s.executeQuery(query);
            while (rs.next()) {
                int itemID = rs.getInt("itemID");
                String name = rs.getString("name");
                String description = rs.getString("description");
                double price = rs.getDouble("price");
                MenuItem item = new MenuItem(itemID, name, description, price);
                itemList.add(item);
            }
        
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            if (s != null) {
                s.close();
            }
        }
        return itemList;
    }
    
    public List<MenuItem> getMenuItems(int itemID) {
        List<MenuItem> filterList = new ArrayList<MenuItem>();
        try {
            itemList = getMenuItems();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for (MenuItem item: itemList) {
            if (item.getItemID() == itemID) {
                filterList.add(item);
            }
        }
        return filterList;
    }
    
    public void addMenuItem(MenuItem item) {
        String sql = "insert into _445team11.Item values " + "(?, ?, ?, ?);";
        
        PreparedStatement prepState = null;
        try {
            prepState = conn.prepareStatement(sql);
            prepState.setInt(1, item.getItemID());
            prepState.setString(2, item.getName());
            prepState.setString(3, item.getDescription());
            prepState.setDouble(4, item.getPrice());
            prepState.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            e.printStackTrace();
        }
    }
    
    public List<Order> getOrdersByCustomer(Customer cust) {
    	String sql = "SELECT orderID, orderDate FROM _445team11.Order"
    			+ " WHERE Customer_userID = '" + cust.getUserID() + "'";
    	ArrayList<Order> orderList = new ArrayList<Order>();

    	try {
    		Statement s = conn.createStatement();
    		ResultSet rs = s.executeQuery(sql);
    		while (rs.next()) {
    			orderList.add(new Order(rs.getInt("orderID"), rs.getDate("orderDate")));
    		}
    	} catch (SQLException e) {
    		System.out.println(e);
    		e.printStackTrace();
    	}
    	return orderList;
    }
    
    public void updateMenuItem(int row, String columnName, Object data) {
    	
    }
    
    public static int getNextOrderID() throws SQLException {
    	int ID = 0;
    	Random rn = new Random();
    	if(conn == null) {
    		createConnection();
    	}
    	Statement s = null;
        String query = "select MAX(orderID) FROM _445team11.Order";
        try {
        	s = conn.createStatement();
        	ResultSet rs = s.executeQuery(query);
        	rs.next();
        	ID = rs.getInt("MAX(orderID)");
        	ID += rn.nextInt(11)+3;
        } catch (SQLException e) {
        	System.out.println(e);
        	e.printStackTrace();
        }
        return ID;
    }
    

    public void addOrder(Customer cus) {
    	System.out.println("Add order reached");
		TreeMap<MenuItem, Integer> orderedItems = (TreeMap<MenuItem, Integer>)cus.getOrder().getLineItems();
		Iterator<?> it = orderedItems.entrySet().iterator();
		
		int orderID = cus.getOrder().getID();
		Date date = cus.getOrder().getDate();
		String cusID = cus.getUserID();
		
		String sql = "insert into _445team11.Order values " + "(?, ?, ?);";
        
        PreparedStatement prepState = null;
        try {
            prepState = conn.prepareStatement(sql);
            prepState.setInt(1, orderID);
            prepState.setDate(2, date);
            prepState.setString(3, cusID);
            prepState.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            e.printStackTrace();
        }
        
		while(it.hasNext()) {
			@SuppressWarnings("rawtypes")
			Map.Entry pairs = (Map.Entry)it.next();
			MenuItem item = (MenuItem) pairs.getKey();
			int quantity = (int) pairs.getValue();
			int itemID = item.getItemID();
			orderID = cus.getOrder().getID();
			
			if (quantity > 0) {
			sql = "insert into _445team11.OrderLine values " + "(?, ?, ?);";
	        
	        prepState = null;
		        try {
		            prepState = conn.prepareStatement(sql);
		            prepState.setInt(1, quantity);
		            prepState.setInt(2, orderID);
		            prepState.setInt(3, itemID);
		            prepState.executeUpdate();
		        } catch (SQLException e) {
		            System.out.println(e);
		            e.printStackTrace();
		        }
				}
		}
		
		
    }
    
    public static void addReview(String comment, int rating, String custID, int itemID) {
    	
    	String sql = "INSERT INTO _445team11.Review(reviewDate, reviewComments, reviewRating,Customer_userID, Item.itemID  VALUES " +
    	" ('" + new Date(System.currentTimeMillis()).toString() + "','" 
    		  + comment +"','" + rating +"','" + custID + "','" + Integer.toString(itemID) + "') ON DUPLICATE KEY"
    		  		+ " UPDATE reviewDate = '" +  new Date(System.currentTimeMillis()).toString() + 
    		  		"', reviewComments = '" + comment + "', reviewRating = '" + rating +"'";
    	System.out.println(sql);
    	
    	try {
			Statement s = DB.conn.createStatement();
			s.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
              
    }
}